package com.qf.java2201.dao.impl;

import com.qf.java2201.dao.UserInfoDao;
import com.qf.java2201.pojo.HUserinfo;
import com.qf.java2201.utils.DbUtils;
import com.qf.java2201.utils.StringUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.List;

/**
 * @date 2022/4/8
 * @desc
 */
public class UserInfoDaoImpl implements UserInfoDao {

    QueryRunner queryRunner = new QueryRunner(DbUtils.getDs());


    @Override
    public List<HUserinfo> findUserInfoByKsid(String tid) {
        // 到公司  不要随意  select *
        String sql="select * from h_userinfo where ksid = ?";

        List<HUserinfo> ksDoctors = null;
        try {
            ksDoctors = queryRunner.query(sql, new BeanListHandler<>(HUserinfo.class), tid);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        return ksDoctors;
    }

    @Override
    public HUserinfo findUserInfoBydid(String did) {
        //预约详情 页面  根据 医生主键 did 查询详情
        // 注意  科室  职位  学历  专业 等信息 是在数据字典表  所以 需要把数据字典表当成多张表
        //来使用  联查
        String sql = "SELECT u.*,t1.tname AS ks,t2.tname AS zw,t3.tname AS xl,t4.tname AS zy " +
                " FROM h_userinfo u JOIN h_type t1 ON u.`ksid` = t1. tid JOIN h_type t2 " +
                "ON u.`zwid` = t2.tid JOIN h_type t3 ON u.`xlid`=t3.tid \n" +
                "\n" +
                " JOIN h_type t4 ON u.`zyid`=t4.tid WHERE u.`did` = ? ;";

        HUserinfo query = null;
        try {
            query = queryRunner.query(sql, new BeanHandler<>(HUserinfo.class), did);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return query;
    }

    @Override
    public List<HUserinfo> getDoctorsPageData(int pageNum, int pageSize, HUserinfo hUserinfo) {

        String sql = "SELECT u.*,t1.tname AS ks,t2.tname AS zw,t3.tname AS xl,t4.tname AS zy " +
                " FROM h_userinfo u JOIN h_type t1 ON u.`ksid` = t1. tid JOIN h_type t2 " +
                "ON u.`zwid` = t2.tid JOIN h_type t3 ON u.`xlid`=t3.tid \n" +
                "\n" +
                " JOIN h_type t4 ON u.`zyid`=t4.tid ";

        StringBuffer sb = new StringBuffer(sql);

        if(StringUtil.isNotEmpty(hUserinfo.getDrealname())){
            sb.append(" and drealname like '%"+ hUserinfo.getDrealname()+"%'");
        }

        if(StringUtil.isNotEmpty(hUserinfo.getDphone())){
            sb.append(" and dphone like '%"+ hUserinfo.getDphone()+"%'");
        }

        if(StringUtil.isNotEmpty(hUserinfo.getKs())){
            sb.append(" and t1.tname like '%"+ hUserinfo.getKs()+"%'");
        }

        sb.append(" limit ?, ?");
         sql = sb.toString().replaceFirst("and" ,"where");

        int start= (pageNum-1) * pageSize;
        List<HUserinfo> doctors = null;
        try {
            doctors = queryRunner.query(sql, new BeanListHandler<>(HUserinfo.class), start, pageSize);

            

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return doctors;
    }

    @Override
    public long getDoctorsCount(HUserinfo hUserinfo) {

        String sql = "SELECT count(*) " +
                " FROM h_userinfo u JOIN h_type t1 ON u.`ksid` = t1. tid JOIN h_type t2 " +
                "ON u.`zwid` = t2.tid JOIN h_type t3 ON u.`xlid`=t3.tid \n" +
                "\n" +
                " JOIN h_type t4 ON u.`zyid`=t4.tid ";

        StringBuffer sb = new StringBuffer(sql);

        if(StringUtil.isNotEmpty(hUserinfo.getDrealname())){
            sb.append(" and drealname like '%"+ hUserinfo.getDrealname()+"%'");
        }

        if(StringUtil.isNotEmpty(hUserinfo.getDphone())){
            sb.append(" and dphone like '%"+ hUserinfo.getDphone()+"%'");
        }

        if(StringUtil.isNotEmpty(hUserinfo.getKs())){
            sb.append(" and t1.tname like '%"+ hUserinfo.getKs()+"%'");
        }

        //查询总记录数
        sql =  sb.toString().replaceFirst("and" ,"where");
        System.out.println(sql);

        try {
            Long count = queryRunner.query(sql, new ScalarHandler<Long>());
            return count;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        return 0;
    }

    @Override
    public int getDnum(String edid) {
        //查询 医生的 最大预约人数
        String sql="select dnum from h_userinfo  where did = ?";

        Integer query = null;
        try {
            query = queryRunner.query(sql, new ScalarHandler<Integer>(), edid);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return query;
    }
}
